﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Com.AlbLab.GEP.Dao.Base;
using Com.AlbLab.GEP.Dao.Interface;
using Com.AlbLab.GEP.Domain.Entities;
using Oracle.DataAccess.Client;
using Com.AlbLab.GEP.Domain;
using System.Data;

namespace Com.AlbLab.GEP.Dao.Impl
{
    public class FasePraticaDao : OracleDao<OracleSessionManager, ISessionModel>, IFasePraticaDao
    {
        public FasePraticaDao(OracleSessionManager daoContext)
            : base(daoContext)
        {
            if (!base.Context.Session_isActive())
            {
                base.Context.Session_Init();
                base.CurrentConnection.Open();
            }
        }

        private string seletctAll = "select * from GEP_FASE";
        private string seletctByIdPratica = "select * from GEP_FASE where FAS_COD_PRATICA = :FAS_COD_PRATICA order by FAS_PROGR_FASE asc";
        private string selectFaseByProgr = "select gep_fase.*, gep_blob.BLB_INTESTAZIONE_DOC as DOC_NAME, gep_blob.BLB_ID_FASE AS DOC_ID from GEP_FASE left outer join gep_blob on gep_fase.fas_id= gep_blob.BLB_ID_FASE where FAS_COD_PRATICA = :FAS_COD_PRATICA AND FAS_PROGR_FASE = :FAS_PROGR_FASE";
        //private string selectById = "select * from GEP_FASE WHERE FAS_ID=:FAS_ID";
        private string selectById = "select gep_fase.*, gep_blob.BLB_INTESTAZIONE_DOC as DOC_NAME, gep_blob.BLB_ID_FASE AS DOC_ID from GEP_FASE left outer join gep_blob on gep_fase.fas_id= gep_blob.BLB_ID_FASE  WHERE FAS_ID=:FAS_ID";
        private string delete = "delete GEP_FASE WHERE FAS_ID=:FAS_ID";

        private string update = "UPDATE GEP_FASE"
                              + " SET FAS_COD_PRATICA = :FAS_COD_PRATICA, FAS_COD_SERVIZIO = :FAS_COD_SERVIZIO, FAS_NRO_FASE = :FAS_NRO_FASE, FAS_DES_FASE = :FAS_DES_FASE,"
                              + " FAS_PROGR_FASE = :FAS_PROGR_FASE, FAS_UNIT_OPER = :FAS_UNIT_OPER, FAS_DT_ARRIVO = :FAS_DT_ARRIVO, FAS_STATO = :FAS_STATO,"
                              + " FAS_MOTIVAZ_FASE = :FAS_MOTIVAZ_FASE,FAS_MOTIVAZ_CHIUSURA= :FAS_MOTIVAZ_CHIUSURA, FAS_DT_INILAV = :FAS_DT_INILAV, FAS_DTORA_APP_PROASM = :FAS_DTORA_APP_PROASM,"
                              + " FAS_DTORA_APP_PROCLI = :FAS_DTORA_APP_PROCLI, FAS_DTORA_APP = :FAS_DTORA_APP, FAS_DT_ATTO_RICH = :FAS_DT_ATTO_RICH,"
                              + " FAS_DT_ATTO_OTT = :FAS_DT_ATTO_OTT, FAS_DT_INISOSP = :FAS_DT_INISOSP, FAS_DT_FINESOSP = :FAS_DT_FINESOSP, FAS_DT_FINELAV = :FAS_DT_FINELAV,"
                              + " FAS_NOTE = :FAS_NOTE"
                              + " WHERE (FAS_ID = :FAS_ID)";

        private string insert = "INSERT INTO GEP_FASE"
                              + " (FAS_COD_PRATICA, FAS_COD_SERVIZIO, FAS_NRO_FASE, FAS_DES_FASE, FAS_PROGR_FASE, FAS_UNIT_OPER, FAS_DT_ARRIVO, FAS_STATO,"
                              + " FAS_MOTIVAZ_FASE,FAS_MOTIVAZ_CHIUSURA, FAS_DT_INILAV, FAS_DTORA_APP_PROASM, FAS_DTORA_APP_PROCLI, FAS_DTORA_APP, FAS_DT_ATTO_RICH, FAS_DT_ATTO_OTT,"
                              + " FAS_DT_INISOSP, FAS_DT_FINESOSP, FAS_DT_FINELAV, FAS_NOTE, FAS_ID)"
                              + " VALUES (:FAS_COD_PRATICA, :FAS_COD_SERVIZIO, :FAS_NRO_FASE, :FAS_DES_FASE, :FAS_PROGR_FASE, :FAS_UNIT_OPER, :FAS_DT_ARRIVO, :FAS_STATO,"
                              + " :FAS_MOTIVAZ_FASE,:FAS_MOTIVAZ_CHIUSURA, :FAS_DT_INILAV, :FAS_DTORA_APP_PROASM, :FAS_DTORA_APP_PROCLI, :FAS_DTORA_APP, :FAS_DT_ATTO_RICH, :FAS_DT_ATTO_OTT,"
                              + " :FAS_DT_INISOSP, :FAS_DT_FINESOSP, :FAS_DT_FINELAV, :FAS_NOTE, ASM_GEP_FASE_ID.nextval)"
                              + " RETURNING FAS_ID INTO :FAS_ID";

        private OracleParameter[] MappingParams(GEP_FASE r, bool isInsert)
        {
            OracleParameter[] oparams;
            oparams = new OracleParameter[21];

            oparams[0] = new OracleParameter("FAS_COD_PRATICA", OracleDbType.Decimal, r.Fas_cod_pratica, ParameterDirection.Input);
            oparams[1] = new OracleParameter("FAS_COD_SERVIZIO", OracleDbType.Varchar2, r.Fas_cod_servizio, ParameterDirection.Input);
            oparams[2] = new OracleParameter("FAS_NRO_FASE", OracleDbType.Decimal, r.Fas_nro_fase, ParameterDirection.Input);
            oparams[3] = new OracleParameter("FAS_DES_FASE", OracleDbType.Varchar2, r.Fas_des_fase, ParameterDirection.Input);
            oparams[4] = new OracleParameter("FAS_PROGR_FASE", OracleDbType.Decimal, r.Fas_Progr_Fase, ParameterDirection.Input);
            oparams[5] = new OracleParameter("FAS_UNIT_OPER", OracleDbType.Varchar2, r.Fas_Unit_Oper, ParameterDirection.Input);
            oparams[6] = new OracleParameter("FAS_DT_ARRIVO", OracleDbType.TimeStamp, r.Fas_Dt_Arrivo, ParameterDirection.Input);
            oparams[7] = new OracleParameter("FAS_STATO", OracleDbType.Varchar2, r.Fas_Stato, ParameterDirection.Input);
            oparams[8] = new OracleParameter("FAS_MOTIVAZ_FASE", OracleDbType.Varchar2, r.Fas_Motivaz_Sosp, ParameterDirection.Input);
            oparams[9] = new OracleParameter("FAS_MOTIVAZ_CHIUSURA", OracleDbType.Varchar2, r.Fas_Motivaz_Chiusura, ParameterDirection.Input);
            oparams[10] = new OracleParameter("FAS_DT_INILAV", OracleDbType.TimeStamp, r.Fas_Dt_Inilav, ParameterDirection.Input);
            oparams[11] = new OracleParameter("FAS_DTORA_APP_PROASM", OracleDbType.TimeStamp, r.Fas_Dtora_App_Proasm, ParameterDirection.Input);
            oparams[12] = new OracleParameter("FAS_DTORA_APP_PROCLI", OracleDbType.TimeStamp, r.Fas_Dtora_App_Procli, ParameterDirection.Input);
            oparams[13] = new OracleParameter("FAS_DTORA_APP", OracleDbType.TimeStamp, r.Fas_Dtora_App, ParameterDirection.Input);
            oparams[14] = new OracleParameter("FAS_DT_ATTO_RICH", OracleDbType.TimeStamp, r.Fas_Dt_Atto_Rich, ParameterDirection.Input);
            oparams[15] = new OracleParameter("FAS_DT_ATTO_OTT", OracleDbType.TimeStamp, r.Fas_Dt_Atto_Ott, ParameterDirection.Input);
            oparams[16] = new OracleParameter("FAS_DT_INISOSP", OracleDbType.TimeStamp, r.Fas_Dt_Inisosp, ParameterDirection.Input);
            oparams[17] = new OracleParameter("FAS_DT_FINESOSP", OracleDbType.TimeStamp, r.Fas_Dt_Finesosp, ParameterDirection.Input);
            oparams[18] = new OracleParameter("FAS_DT_FINELAV", OracleDbType.TimeStamp, r.Fas_Dt_Finelav, ParameterDirection.Input);
            oparams[19] = new OracleParameter("FAS_NOTE", OracleDbType.Varchar2, r.Fas_Note, ParameterDirection.Input);
            
            if (isInsert)
            {
                oparams[20] = new OracleParameter("FAS_ID", OracleDbType.Decimal, r.Fas_Id, ParameterDirection.Output);
            
            }
            else
                oparams[20] = new OracleParameter("FAS_ID", OracleDbType.Decimal, r.Fas_Id, ParameterDirection.Input);
            

            return oparams;
        }

        #region IDao<GEP_FASE,decimal> Members

        public Dictionary<decimal, GEP_FASE> GetAll()
        {
            throw new NotImplementedException();
        }

        public List<GEP_FASE> GetByPraticaId(decimal idPratica)
        {
            Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(seletctByIdPratica, CurrentConnection);
            cmd.Parameters.Add("FAS_COD_PRATICA", idPratica);
            List<GEP_FASE> list = null;
            try
            {
                using (OracleDataReader r = cmd.ExecuteReader())
                {
                    if (r.HasRows)
                    {
                        list = new List<GEP_FASE>();
                        while (r.Read())
                        {
                            GEP_FASE t = DaoOracleDbHelper.MapFaseList(r);
                            list.Add(t);
                        }
                    }
                }
            }
            catch (Exception e0x)
            {
                throw new ManagedException("EX-FS003", e0x);
            }
            return list; 
        }

        public GEP_FASE GetById(decimal id)
        {
            Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(selectById, CurrentConnection);
            cmd.Parameters.Add("FAS_ID", id);
            GEP_FASE ut = null;
            try
            {
                using (OracleDataReader r = cmd.ExecuteReader())
                {
                    if (r.HasRows)
                        r.Read();
                    ut = DaoOracleDbHelper.MapFase(r);
                }
            }
            catch (Exception e0x)
            {
                throw new ManagedException("EX-FS002", e0x);
            }
            return ut;
        }

        public decimal GetByProgr(decimal prt, decimal prg)
        {
            decimal response = 0;
            Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(selectFaseByProgr, CurrentConnection);
            cmd.Parameters.Add("FAS_COD_PRATICA", prt);
            cmd.Parameters.Add("FAS_PROGR_FASE", prg);
            GEP_FASE ut = null;
            try
            {
                using (OracleDataReader r = cmd.ExecuteReader())
                {
                    if (r.HasRows)
                    {
                        r.Read();
                        ut = DaoOracleDbHelper.MapFase(r);
                        response = ut.Fas_Id;
                    }
                }
            }
            catch (Exception e0x)
            {
                throw new ManagedException("EX-FS012", e0x);
            }
            return response;
        
        }
        public GEP_FASE GetAndLock(decimal id)
        {
            Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(selectById + " FOR UPDATE", CurrentConnection);
            cmd.Parameters.Add("FAS_ID", id);
            GEP_FASE ut = null;
            try
            {
                using (OracleDataReader r = cmd.ExecuteReader())
                {
                    if (r.HasRows)
                        r.Read();
                    ut = DaoOracleDbHelper.MapFase(r);
                }
            }
            catch (Exception e0x)
            {
                throw new ManagedException("EX-FS0U2", e0x);
            }
            return ut;
        }


        public void Insert(GEP_FASE entity)
        {
            using (OracleCommand ocmd = new OracleCommand())
            {
                try
                {
                    ocmd.CommandText = insert;
                    ocmd.Parameters.AddRange(MappingParams(entity, true));
                    ocmd.Connection = base.CurrentConnection;
                    ocmd.ExecuteNonQuery();
                    entity.Fas_Id = (decimal)((Oracle.DataAccess.Types.OracleDecimal)ocmd.Parameters["FAS_ID"].Value);
                }
                catch (Exception e0x)
                {
                    throw new ManagedException("EX-FS005", e0x);
                }
            }
        }

        public void Update(GEP_FASE entity)
        {
            using (OracleCommand ocmd = new OracleCommand())
            {
                try
                {
                    ocmd.CommandText = update;
                    ocmd.Parameters.AddRange(MappingParams(entity, false));
                    ocmd.Connection = base.CurrentConnection;
                    ocmd.ExecuteNonQuery();
                }
                catch (Exception e0x)
                {
                    throw new ManagedException("EX-FS005", e0x);
                }
            }
        }

        public void Delete(decimal id)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            if (!base.Context.TransactionModeOn)
            {
                base.CurrentConnection.Close();
            }
        }

        #endregion
    }
}